﻿
import MySQLdb
#因为我要跨进程操作内存数据库，网站主要是读，而抓取进程主要是写。
#而且还要定时转存到硬盘上以防止数据丢失。
#目前没有办法多进程操作同一内存数据库。
#本类的作用就是同一个进程专门来管理这些事
#这样虽然让程序灵活了，但是由于通过此进程中转一道
#内存也多浪费了一份，还有进程间的通信开销。。。

# 内存数据库的方式不为主流圈所采用，所以不准备采用这种方式
# 采用cache的方式来解决这类问题
class db_manager:
	def __init__(self):
		pass
	
	def connect_db(self, db_name = 'itebooks'):
		self.conn = MySQLdb.connect(host = 'localhost', user = 'root', passwd = 'psd-9898w', db = db_name)
		cursor = conn.cursor()
		cursor.execute("SET NAMES utf8")
		cursor.execute("SET CHARACTER_SET_CLIENT=utf8")
		cursor.execute("SET CHARACTER_SET_RESULTS=utf8")
		cursor.execute('SET AUTOCOMMIT = 1')
		
	def execute_sql(self, sql, param = None):
		if not self.conn:
			print "error, cannot connect to db"
			return
		if not self.conn.cursor:
			print "can not get cursor"
			return
		cursor = self.conn.cursor
		if param:
			cursor.execute(sql, param)
		else:
			cursor.execute(sql)
		return cursor
		
	def execute_scaler(self, sql, param):
		cursor = self.execute_sql(sql, param)
		row = cursor.fetchone()
		return row[0]
		
	def persist_memery_db(self, mem_tb_name, pres_tb_name):
		sql = '''lock tables %s write, %s read; insert into %s select * from %s; unlock tables;''' % (pres_tb_name, mem_tb_name, mem_tb_name, pres_tb_name)
		self.execute_sql(sql)
		
	def log_sql(self, sql): #相异引擎表同步方式不通用，不准备采用，此函数暂不写了
		#cuz some sql have bin data so i dont know how to log it
		#只记录insert/update/delete，只记录(log_id, op_time, id, op_type)即可
		#如果是insert，select last_insert_rowid() newid;可取得id
		#非并发安全
		pass
	
	def execute_log(self, log_item):
		pass
		
	def update_from_log(self, log_file):
		#id = self.current_log_id()
		#for curid in log_ids(> id):
		#	log_item = item_by_id(curid)
		#	self.execute_log(log_item)
		pass

class db_ebook:
	def connect_db(self, db_name = 'itebook.db'):
		conn = MySQLdb.connect(host = 'localhost', user = 'root', passwd = 'psd-9898w', db = 'itebooks')
		self.conn = conn
		cursor = conn.cursor()
		cursor.execute("SET NAMES utf8")
		cursor.execute("SET CHARACTER_SET_CLIENT=utf8")
		cursor.execute("SET CHARACTER_SET_RESULTS=utf8")
		cursor.execute('SET AUTOCOMMIT = 1')

	def add_author(self, src_url, name):
		cursor = self.conn.cursor()
		sql = '''insert into author(src_url, name) value(%s, %s);'''
		cursor.execute(sql, (src_url, name))

	def is_author_exists(self, src_url):
		cursor = self.conn.cursor()
		sql = '''select count(0) from author where src_url=%s;'''
		cursor.execute(sql, (src_url, ))
		row = cursor.fetchone()
		return row[0] > 0

	def add_tag(self, src_url, name):
		cursor = self.conn.cursor()
		sql = '''insert into tag(src_url, name) value(%s, %s);'''
		cursor.execute(sql, (src_url, name))

	def is_tag_exists(self, src_url):
		cursor = self.conn.cursor()
		sql = '''select count(0) from tag where src_url=%s;'''
		cursor.execute(sql, (src_url, ))
		row = cursor.fetchone()
		return row[0] > 0

	def add_publisher(self, src_url, name):
		cursor = self.conn.cursor()
		sql = '''insert into publisher(src_url, name) value(%s, %s);'''
		cursor.execute(sql, (src_url, name))

	def is_publisher_exists(self, src_url):
		cursor = self.conn.cursor()
		sql = '''select count(0) from publisher where src_url=%s;'''
		cursor.execute(sql, (src_url, ))
		row = cursor.fetchone()
		return row[0] > 0

	def book_click(self, bookid, userip):
		cursor = self.conn.cursor()
		sql = '''insert into book_click(bookid, userip) value(%s, %s);'''
		cursor.execute(sql, (bookid, userip))

	def book_buy(self, bookid, userip):
		cursor = self.conn.cursor()
		sql = '''insert into book_buy(bookid, userip) value(%s, %s);'''
		cursor.execute(sql, (bookid, userip))

	def book_download(self, bookid, userip):
		cursor = self.conn.cursor()
		sql = '''insert into book_download(bookid, userip) value(%s, %s);'''
		cursor.execute(sql, (bookid, userip))

	def book_read(self, bookid, userip):
		cursor = self.conn.cursor()
		sql = '''insert into book_read(bookid, userip) value(%s, %s);'''
		cursor.execute(sql, (bookid, userip))

	def is_book_exists(self, src_url):
		cursor = self.conn.cursor()
		sql = '''select count(0) from book where src_url=%s;''' #% src_url
		cursor.execute(sql, (src_url, ))
		row = cursor.fetchone()
		return row[0] > 0

	def get_book_id(self, src_url):
		cursor = self.conn.cursor()
		sql = '''select id from book where src_url=%s;'''
		cursor.execute(sql, (src_url, ))
		row = cursor.fetchone()
		return row[0]

	def get_tag_id(self, src_url):
		cursor = self.conn.cursor()
		sql = '''select id from tag where src_url=%s;'''
		cursor.execute(sql, (src_url, ))
		row = cursor.fetchone()
		return row[0]

	def get_author_id(self, src_url):
		cursor = self.conn.cursor()
		sql = '''select id from author where src_url=%s;'''
		cursor.execute(sql, (src_url, ))
		row = cursor.fetchone()
		return row[0]

	def get_publisher_id(self, src_url):
		cursor = self.conn.cursor()
		sql = '''select id from publisher where src_url=%s;'''
		cursor.execute(sql, (src_url, ))
		row = cursor.fetchone()
		return row[0]

	def add_book(self, src_url, title, title1, img_url, isbn, datepublished, pages, language, file_size, file_format, down_url, buy_url, read_url, description):
		cursor = self.conn.cursor()
		sql = '''insert into book(src_url, title, title1, img_url, isbn, datepublished, pages, language, file_size, file_format, down_url, buy_url, read_url, description) value(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);'''
		cursor.execute(sql, (src_url, title, title1, img_url, isbn, str(datepublished), str(pages), language, file_size, file_format, down_url, buy_url, read_url, description))

	def add_book_author(self, bookid_authorids):
		cursor = self.conn.cursor()
		sql = '''insert into book_author(bookid, authorid) values (%s, %s);'''
		cursor.executemany(sql, list(bookid_authorids))

	def add_book_publisher(self, bookid_publisherids):
		cursor = self.conn.cursor()
		sql = '''insert into book_publisher(bookid, publisherid) values (%s, %s);'''
		cursor.executemany(sql, list(bookid_publisherids))

	def add_book_tag(self, bookid_tagids):
		cursor = self.conn.cursor()
		sql = '''insert into book_tag(bookid, tagid) values (%s, %s);'''
		cursor.executemany(sql, list(bookid_tagids))

if __name__ == '__main__':
	print '你好, world'
